/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.vasthu.core.dao;

import com.vasthu.api.dao.StockDAO;
import com.vasthu.api.domain.Stock;
import com.vasthu.api.domain.User;
import com.vasthu.utility.VstDBManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author moremada
 */
public class StockDAOImpl implements StockDAO {

    static Logger logger = Logger.getLogger(StockDAOImpl.class);

    @Override
    public boolean insertStock(Stock stock) {
        if (stock == null) {
            return false;
        }
        PreparedStatement pst = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("INSERT INTO Stock(purchase_order_id, dispached_date, "
                    + "purchased_price, quantity, unit_price, min_price, max_price,"
                    + " 	discount, expiry_date, item_id, status) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

            pst.setString(1, stock.getPurchaseOrderId());
            pst.setDate(2, new java.sql.Date(stock.getDispachedDate().getTime()));
            pst.setFloat(3, stock.getPurchasedPrice());
            pst.setFloat(4, stock.getQuantity());
            pst.setFloat(5, stock.getUnitPrice());
            pst.setFloat(6, stock.getMinPrice());
            pst.setFloat(7, stock.getMaxPrice());
            pst.setFloat(8, stock.getDiscount());
            pst.setDate(9, new java.sql.Date(stock.getExpiryDate().getTime()));
            pst.setLong(10, stock.getItemId());
            pst.setInt(11, stock.getStatus());

            pst.executeUpdate();

        } catch (Exception ex) {
            System.out.println("ERROR : StockDAOImpl->insertStock()");
            System.out.println(ex);
        } finally {
            VstDBManager.get().close();
        }

        return true;
    }

    @Override
    public Stock getStockById(long stockId) {
        PreparedStatement pst = null;
        ResultSet rs = null;
        Stock stock = new Stock();
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("SELECT * FROM Stock WHERE stock_id = ?");
            pst.setLong(1, stockId);
            //pst.setInt(2, tatus);
            rs = pst.executeQuery();

            while (rs.next()) {
                stock.setDiscount(rs.getFloat("discount"));
                stock.setDispachedDate(rs.getDate("dispached_date"));
                stock.setExpiryDate(rs.getDate("expiry_date"));
                stock.setItemId(rs.getLong("item_id"));
                stock.setMaxPrice(rs.getFloat("max_price"));
                stock.setMinPrice(rs.getFloat("min_price"));
                stock.setPurchaseOrderId(rs.getString("purchase_order_id"));
                stock.setPurchasedPrice(rs.getFloat("purchased_price"));
                stock.setQuantity(rs.getFloat("quantity"));
                stock.setStockId(rs.getLong("stock_id"));
                stock.setUnitPrice(rs.getFloat("unit_price"));
                break;
            }

        } catch (Exception ex) {
            System.out.println("ERROR : StockDAOImpl->getStockById()");
        } finally {
            VstDBManager.get().close();
        }

        return stock;
    }

    @Override
    public boolean deleteStockById(long stockId) {
        PreparedStatement pst = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("DELETE FROM Stock WHERE stock_id = ?");
            pst.setLong(1, stockId);
            pst.executeUpdate();
        } catch (Exception ex) {
            logger.error("unable to delete : " + ex);
        } finally {
            VstDBManager.get().close();
        }

        return true;
    }

    @Override
    public List<Stock> getAllStockItems() {
        List<Stock> stockList = new ArrayList<Stock>();
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("SELECT * FROM Stock WHERE status = 1");
            rs = pst.executeQuery();

            while (rs.next()) {
                Stock stock = new Stock();

                stock.setStockId(rs.getInt("stock_id"));
                stock.setPurchaseOrderId(rs.getString("purchase_order_id"));
                stock.setDispachedDate(rs.getDate("dispached_date"));
                stock.setPurchasedPrice(rs.getFloat("purchased_price"));
                stock.setQuantity(rs.getFloat("quantity"));
                stock.setUnitPrice(rs.getFloat("unit_price"));
                stock.setMinPrice(rs.getFloat("min_price"));
                stock.setMaxPrice(rs.getFloat("max_price"));
                stock.setDiscount(rs.getFloat("discount"));
                stock.setExpiryDate(rs.getDate("expiry_date"));
                stock.setItemId(rs.getInt("item_id"));
                stockList.add(stock);
            }

        } catch (SQLException ex) {
            System.out.println("ERROR : StockDAOImpl->getAllStockedItems()");
        } finally {
            VstDBManager.get().close();
        }
        return stockList;
    }

    @Override
    public boolean updateQuantity(Stock stock) {
        if (stock == null) {
            return false;
        }
        if (stock.getQuantity() < 0) {
            logger.error("Invalid quantity : " + stock.getQuantity());
            return false;
        }
        Connection con = VstDBManager.get().getConnection();
        PreparedStatement pst = null;
        try {
            pst = con.prepareStatement("UPDATE Stock SET quantity = ? WHERE stock_id = ? ");
            pst.setFloat(1, stock.getQuantity());
            pst.setLong(2, stock.getStockId());
            pst.executeUpdate();
        } catch (Exception ex) {
            logger.error("unable to update quantity : " + ex);
        } finally {
            VstDBManager.get().close();
        }
        return true;
    }
}
